<?php
/**
 * 家具管理model
 * author:ty
 * date:2017/08/22
 */
namespace App\Http\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Fitment extends Model
{
    protected $table='fitments';

    /**家具品牌*/
    public static function brand()
    {
        $res = Brand::where('brand_status',1)->get();
        return $res;
    }
    /**
     * 家具列表
     * @param 查询条件
     */
    public static function lists($param)
    {
        /**家具主表查询条件start*/
        $filter = array();
        if(isset($param['fitment_brand']) && $param['fitment_brand'] !=''){ /**品牌搜索*/
            $filter[] = " fts.`fitments_brand_id` = {$param['fitment_brand']}";
        }
        if(isset($param['fitment_type_classify']) && $param['fitment_type_classify'] !=''){ /**家具分类[办公，家用，商用]*/
            $filter[] = " fts.`fitments_type` = {$param['fitment_type_classify']}";
        }
        if(isset($param['fitment_subcate_id']) && $param['fitment_subcate_id'] !=''){ /**家具分类[家具部件]*/
            $filter[] = " fts.`fitments_subcate_id` = {$param['fitment_subcate_id']}";
        }
        $where = ' fts.`fitments_isdel`=1 ';
        if ( count($filter) > 0 ){
            $where .=  ' AND '.implode(' AND ', $filter);
        }
        $currentpage = ($param['current_page']-1)*$param['per_page'];
        $limit = " LIMIT {$currentpage},{$param['per_page']} ";/**家具主表查询条件end*/
        /**家具详情查询条件start*/
        $price = array(
            '1'=>array('min'=>'100','max'=>'500'),
            '2'=>array('min'=>'500','max'=>'1000'),
            '3'=>array('min'=>'1000','max'=>'2000'),
        );
        $filterdt = array();
        if(isset($param['fitment_name']) && $param['fitment_name'] != '') { /**家具名搜索*/
            $filterdt[] = "(fts.`fitments_name` like '%{$param['fitment_name']}%')";
        }
        if($param['fitment_rent']==1) { /**租赁方式选择*/
            if(isset($param['fitment_price']) && $param['fitment_price'] !=''){ /**租赁价格*/
                $filterdt[] = " dt.`details_fixprice` >= {$price[$param['fitment_price']]['min']} AND dt.`details_fixprice` <= {$price[$param['fitment_price']]['max']} ";
            }
            if((isset($param['minprice']) && $param['minprice']!='') && (isset($param['maxprice']) && $param['maxprice']!='')){
                $filterdt[] = " dt.`details_fixprice` >= {$param['minprice']} AND dt.`details_fixprice` <= {$param['maxprice']} ";
            }
        } else {
            if(isset($param['fitment_price']) && $param['fitment_price'] !=''){ /**租赁价格*/
                $filterdt[] = " dt.`details_nimbleprice` >= {$price[$param['fitment_price']]['min']} AND dt.`details_nimbleprice` <= {$price[$param['fitment_price']]['max']} ";
            }
            if((isset($param['minprice']) && $param['minprice']!='') && (isset($param['maxprice']) && $param['maxprice']!='')){
                $filterdt[] = " dt.`details_nimbleprice` >= {$param['minprice']} AND dt.`details_nimbleprice` <= {$param['maxprice']} ";
            }
        }
        $wheredt = '';
        if ( count($filterdt) > 0 ){
            $wheredt .= implode(' AND ', $filterdt);
            $wheredt = ' AND '.$wheredt;
        }

        $order = '';
        if(isset($param['fitment_orderby']) && $param['fitment_orderby'] !='') {
            if($param['fitment_orderby']=='1'){
                $order .= ' ORDER BY price DESC ';
            }else{
                $order .= ' ORDER BY price ASC ';
            }
        }else{
            $order .= '  ORDER BY price ASC ';
        };
        $order .= ' ,dt.`details_isdel` DESC ';

        /***家具列表查询修改*/
        $sql = 'SELECT * FROM mzj_fitment_details AS dt1  GROUP BY details_pid ';
        /***固定租*/
        if($param['fitment_rent']==1) {
            $res=DB::select('SELECT 
(SELECT GROUP_CONCAT(DISTINCT(`mzj_color`.`color_type`)) AS color_types FROM `mzj_fitment_details` AS `mzj_dt1` LEFT JOIN `mzj_fitment_details` AS `mzj_dt2` ON `mzj_dt1`.`details_pid` = `mzj_dt2`.`details_pid` LEFT JOIN `mzj_fitment_color` AS `mzj_color` ON `mzj_color`.`color_details_id` = `mzj_dt2`.`details_id` WHERE `mzj_dt1`.`details_id` = dt.`details_id`) AS colors,
img.`fitment_details_imgOne`,dt.`details_name`,dt.`details_describe`,dt.`details_fixprice` AS price ,
dt.`details_id`,
fts.`fitments_name`,fts.`fitments_describe`,fts.`fitments_brand_id`,fts.`fitments_type`,fts.`fitments_subcate_id`,fts.`fitments_id` 
FROM mzj_fitments AS fts 

JOIN (  '.$sql.' ORDER BY dt1.`details_fixprice`) AS dt ON dt.`details_pid`=fts.`fitments_id` 
LEFT JOIN mzj_details_img AS img ON dt.details_id = img.`fitment_details_id` 
WHERE  '.$where.$wheredt.$order.$limit);
        }
        /**随租*/
        else
        {
            $res=DB::select('SELECT 
(SELECT GROUP_CONCAT(DISTINCT(`mzj_color`.`color_type`)) AS color_types FROM `mzj_fitment_details` AS `mzj_dt1` LEFT JOIN `mzj_fitment_details` AS `mzj_dt2` ON `mzj_dt1`.`details_pid` = `mzj_dt2`.`details_pid` LEFT JOIN `mzj_fitment_color` AS `mzj_color` ON `mzj_color`.`color_details_id` = `mzj_dt2`.`details_id` WHERE `mzj_dt1`.`details_id` = dt.`details_id`) AS colors,
img.`fitment_details_imgOne`,
dt.`details_name`,dt.`details_describe`,dt.`details_nimbleprice` AS price ,dt.`details_id`,
fts.`fitments_name`,fts.`fitments_describe`,fts.`fitments_brand_id`,fts.`fitments_type`,fts.`fitments_subcate_id`,fts.`fitments_id`  
FROM mzj_fitments AS fts 

 JOIN (  '.$sql.' ORDER BY dt1.`details_nimbleprice`) AS dt ON dt.`details_pid`=fts.`fitments_id` 
LEFT JOIN mzj_details_img AS img ON dt.details_id = img.`fitment_details_id` 
WHERE  '.$where.$wheredt.$order.$limit);
        }
        return $res;

    }

    /**
     * 家具详情
     * @id 家具详情id
     */
    public static function show($id)
    {
        $res = DB::table('fitment_details as dt')
            ->join('fitments as ft','dt.details_pid','=','ft.fitments_id')
            ->leftJoin('details_img as img','dt.details_id','=','img.fitment_details_id')
            ->where('dt.details_isdel',1)
            ->where('dt.details_isdel',1)
            ->where('dt.details_id',$id)
            ->select('dt.details_id','dt.details_fixprice','dt.details_nimbleprice','dt.details_describe',
                'dt.details_bond','dt.details_length','dt.details_width','dt.details_height','dt.details_name',
                'img.fitment_details_imgOne','img.fitment_details_imgTwo','img.fitment_details_imgThree',
                'img.fitment_details_imgFour','img.fitment_details_imgFive','ft.fitments_name','ft.fitments_describe')
            ->first();
        return $res;
    }

    /**家具规格详情*/
    public static function details($id)
    {
        $res = DB::table('fitment_details as dt')
            ->join('fitments as ft','dt.details_pid','=','ft.fitments_id')
            ->where(function ($query) use ($id){
              $query->where('dt.details_pid',DB::table('fitment_details')->where('details_id',$id)->value('details_pid'));
            })
            ->where('dt.details_isdel',1)
            ->select('dt.details_length','dt.details_width','dt.details_id','ft.fitments_name','ft.fitments_describe','dt.details_name','ft.fitments_contents')
            ->orderBy('dt.details_id','asc')
            ->get();

        return $res;
    }

    /**组合家具*/
    public static function combine()
    {
        $res = DB::table('combine')->where('combine_status',1)->select('id','combine_id','combine_price')->orderBy('combine_price','asc')->get();
        $da = array();
        foreach ($res as $v){
            $da[$v->id]['combine_id']=explode(',',(string)$v->combine_id);
            $da[$v->id]['price'] = $v->combine_price;
        }
        return $da;
    }

    /**组合id查询相关价格，图片*/
    public static function combinedet($combine)
    {
        $res = DB::table('fitment_details as dt')
            ->join('details_img as img','dt.details_id','=','img.fitment_details_id')
            ->whereIn('dt.details_id',$combine)
            ->where('dt.details_isdel','1')
            ->select('dt.details_id','dt.details_fixprice','img.fitment_details_imgMaster')
            ->get();
        return $res;
    }

    /**家具id获取关联id*/
    public static function fitment_dts($pid)
    {
        $res = DB::table('fitments as ft')
            ->leftJoin('fitment_details as dt','ft.fitments_id','=','dt.details_pid')
            ->where('ft.fitments_id',$pid)
            ->where('ft.fitments_isdel',1)
            ->where('dt.details_isdel',1)
            ->select('ft.fitments_id', 'dt.details_id','dt.details_name','dt.details_length','dt.details_width', 'dt.details_height','dt.details_describe')
            ->get();
        return $res;
    }

    /**
     * 同家具颜色【颜色去重】
     * @dtid家具详情id
     */
    public static function fitment_colors($dtid)
    {
        $res = DB::table('fitment_details as dt1')
            ->leftJoin('fitment_details as dt2','dt1.details_pid','=','dt2.details_pid')
            ->leftJoin('fitment_color as color','color.color_details_id','=','dt2.details_id')
            ->where('dt1.details_id',$dtid)
            ->groupby('color_type')
            ->get();
        return $res;
    }
}
